﻿

'*******************************************************************
' Program: pkgVisio
' Author: Albert E Edlund
' Date:
'
' Purpose:
' Working with office object XML
'
'*******************************************************************


#Region " imports "

Imports System
Imports System.Object
Imports System.IO
Imports System.IO.TextReader
' need to reference windowsbase in .net
Imports System.IO.Packaging

' need for dbconnectionstringbuilder used in clsConnections
Imports System.Data.Common

Imports System.Windows
Imports System.Windows.Forms
Imports System.String
Imports System.Char
Imports System.Text.StringBuilder
Imports System.Convert
Imports System.Windows.Media
Imports Microsoft.VisualBasic

Imports System.Text
Imports System.Collections
Imports System.Collections.Generic
Imports System.Collections.ObjectModel

Imports System.Xml
Imports System.Linq
Imports System.Linq.Enumerable
Imports System.Windows.Input

Imports System.Xml.Serialization
Imports System.Xml.Linq
Imports System.Xml.XPath

Imports ADODB
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.Sql


Imports Microsoft.Practices.EnterpriseLibrary.Common
Imports Microsoft.Practices.EnterpriseLibrary.ExceptionHandling
Imports Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Logging


Imports DocumentFormat
Imports DocumentFormat.OpenXml.Packaging



' visio v15 does not use a xName prefix which may put it into conflict with other open office apps
Imports <xmlns="http://schemas.microsoft.com/office/visio/2012/main">
Imports <xmlns:rs="urn:schemas-microsoft-com:rowset">

' generic namespace for office open xml, used in all of them
Imports <xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">



' powerpoint
'Imports <xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
'Imports <xmlns:p="http://schemas.openxmlformats.org/presentationml/2006/main">
' excel does not use a xName prefix for it's basic workbook
' Imports <xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
' word uses a lot 
'Imports <xmlns:wpc="http://schemas.microsoft.com/office/word/2010/wordprocessingCanvas">
'Imports <xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006">
'Imports <xmlns:o="urn:schemas-microsoft-com:office:office">
'Imports <xmlns:m="http://schemas.openxmlformats.org/officeDocument/2006/math">
'Imports <xmlns:v="urn:schemas-microsoft-com:vml">
'Imports <xmlns:wp14="http://schemas.microsoft.com/office/word/2010/wordprocessingDrawing">
'Imports <xmlns:wp="http://schemas.openxmlformats.org/drawingml/2006/wordprocessingDrawing">
'Imports <xmlns:w10="urn:schemas-microsoft-com:office:word">
'Imports <xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main">
'Imports <xmlns:w14="http://schemas.microsoft.com/office/word/2010/wordml">
'Imports <xmlns:wpg="http://schemas.microsoft.com/office/word/2010/wordprocessingGroup">
'Imports <xmlns:wpi="http://schemas.microsoft.com/office/word/2010/wordprocessingInk">
'Imports <xmlns:wne="http://schemas.microsoft.com/office/word/2006/wordml">
'Imports <xmlns:wps="http://schemas.microsoft.com/office/word/2010/wordprocessingShape">


Imports pkgVisio.clsStructures



#End Region


Public Class clsVisPackage


#Region " Structures and Dictionaries"


    '
    ' There are some functions where it is advantageous to have an inventory of
    ' pages and shapes within a document. These deal with mapping datarows to shapes
    ' and validation of rules and shapes. For these functions it is necessary to analyze
    ' all of the pages and shapes.
    '
    Private _dictPageShape As Dictionary(Of stcPageShape, stcShapeInfo)
    Public Property dictPageShape As Dictionary(Of stcPageShape, stcShapeInfo)
        Get
            Return Me._dictPageShape
        End Get
        Set(value As Dictionary(Of stcPageShape, stcShapeInfo))
            If (value Is Nothing) Then
                Throw New System.ArgumentNullException("Value", _
                    "dictPageShape is Null.")
            End If
            Me._dictPageShape = value
        End Set
    End Property

    '
    '
    '
    Private m_dictPagesID As Dictionary(Of Integer, stcPageData)
    Public Property dictPagesID As Dictionary(Of Integer, stcPageData)
        Get
            dictPagesID = Me.m_dictPagesID
        End Get
        Set(value As Dictionary(Of Integer, stcPageData))
            Me.m_dictPagesID = value
        End Set
    End Property

    '
    '
    '
    Private _dictCellSect As Dictionary(Of String, Short)

    '
    ' storage for namespaces
    '
    Private m_nsDict As Dictionary(Of String, System.Xml.Linq.XNamespace)
    Public Property nsDict As Dictionary(Of String, System.Xml.Linq.XNamespace)
        Get
            nsDict = Me.m_nsDict
        End Get
        Set(value As Dictionary(Of String, System.Xml.Linq.XNamespace))
            Me.m_nsDict = value
        End Set
    End Property

    '
    ' storage for attributes
    '
    Private m_attDict As Dictionary(Of String, String)
    Public Property attDict As Dictionary(Of String, String)
        Get
            attDict = Me.m_attDict
        End Get
        Set(value As Dictionary(Of String, String))
            Me.m_attDict = value
        End Set
    End Property

    '
    ' storage for elements
    '
    Private m_elemDict As Dictionary(Of String, String)
    Public Property elemDict As Dictionary(Of String, String)
        Get
            elemDict = Me.m_elemDict
        End Get
        Set(value As Dictionary(Of String, String))
            Me.m_elemDict = value
        End Set
    End Property



#End Region


#Region " Properties and Declarations "




    Public Const doctypeVisio As String = "visio"
    Public Const doctypeExcel As String = "excel"
    Public Const doctypeWord As String = "word"
    Public Const doctypePwrPnt As String = "powerpoint"
    Public Const doctypeUnknown As String = "unknown"




    Private m_docType As String
    Public Property docType As String
        Get
            docType = Me.m_docType
        End Get
        Set(value As String)
            Me.m_docType = value
        End Set
    End Property

    Public m_xDoc As XDocument
    Public Property xDoc As XDocument
        Get
            Return Me.m_xDoc
        End Get
        Set(value As XDocument)
            If (value Is Nothing) Then
                Throw New System.ArgumentNullException("Value", _
                    "xDoc is Null.")
            End If
            Me.m_xDoc = value
        End Set
    End Property

    Private m_fileCurrent As String
    Public Property fileCurrent As String
        Get
            fileCurrent = Me.m_fileCurrent
        End Get
        Set(value As String)
            If ((value Is Nothing) Or (value = "")) Then
                Throw New System.ArgumentNullException("Value", _
                    "fileCurrent is Null.")
            End If
            Me.m_fileCurrent = value
        End Set
    End Property

    Private m_pkgCurrent As Package = Nothing
    Public Property pkgCurrent() As Package
        Get
            pkgCurrent = Me.m_pkgCurrent
        End Get
        Set(ByVal value As Package)
            If (value Is Nothing) Then
                Throw New System.ArgumentNullException("Value", _
                    "pkgCurrent is Null.")
            End If
            Me.m_pkgCurrent = value
        End Set
    End Property

    Private m_LastUri As Uri
    Public Property _LastUri() As Uri
        Get
            _LastUri = Me.m_LastUri
        End Get
        Set(ByVal value As Uri)
            Me.m_LastUri = value
        End Set
    End Property

    '
    ' pkgpartInfo is the collection of content and file pointers in the package
    '
    Private m_colPkgPartInfo As Collection(Of pkgpartInfo)
    Public Property colPkgPartInfo As Collection(Of pkgpartInfo)
        Get
            colPkgPartInfo = Me.m_colPkgPartInfo
        End Get
        Set(value As Collection(Of pkgpartInfo))
            If (value Is Nothing) Then
                Throw New System.ArgumentNullException("Value", _
                    "colPkgPartsInfo is Null.")
            End If
            Me.m_colPkgPartInfo = value
        End Set
    End Property


#Region " relationship and content constants "



    ' office relationship ids
    Public Const relRidDocument As String = "http://schemas.microsoft.com/visio/2010/relationships/document"
    Public Const relRidCore As String = "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties"
    Public Const relRidExtended As String = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties"
    Public Const relRidCustom As String = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties"
    Public Const relRidBase As String = "http://schemas.openxmlformats.org/package/2006/relationships"
    ' visio relationships
    Public Const relRidMasters As String = "http://schemas.microsoft.com/visio/2010/relationships/masters"
    Public Const relRidMaster As String = "http://schemas.microsoft.com/visio/2010/relationships/master"
    Public Const relRidPages As String = "http://schemas.microsoft.com/visio/2010/relationships/pages"
    Public Const relRidPage As String = "http://schemas.microsoft.com/visio/2010/relationships/page"
    Public Const relRidWindows As String = "http://schemas.microsoft.com/visio/2010/relationships/windows"
    Public Const relRidVbaProject As String = "http://schemas.microsoft.com/office/2006/relationships/vbaProject"
    Public Const relRidDataConnections As String = "http://schemas.microsoft.com/visio/2010/relationships/connections"
    Public Const relRidRecordsets As String = "http://schemas.microsoft.com/visio/2010/relationships/recordsets"
    Public Const relRidValidation As String = "http://schemas.microsoft.com/visio/2010/relationships/validation"
    Public Const relRidActiveX As String = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image"

    Public Const o15RidDocument As String = "http://schemas.microsoft.com/office/visio/2012/main"
    Public Const o15RidVtProp As String = "http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"


    ' these are some of the content types
    Public Const contXmlApp As String = "application/vnd.openxmlformats-officedocument.extended-properties+xml"
    Public Const contXmlCore As String = "application/vnd.openxmlformats-package.core-properties+xml"
    Public Const contXmlVisioDocM = "application/vnd.ms-visio.drawing.macroEnabled.main+xml"
    Public Const contXmlVisioDoc = "application/vnd.ms-visio.drawing.main+xml"
    Public Const contXmlWordDoc = "application/vnd.openxmlformats-officedocument.wordprocessingml.document.main+xml"
    Public Const contXmlWindows = "application/vnd.ms-visio.windows+xml"
    Public Const contXmlPages = "application/vnd.ms-visio.pages+xml"
    Public Const contXmlPage = "application/vnd.ms-visio.page+xml"
    Public Const contXmlMasters = "application/vnd.ms-visio.masters+xml"
    Public Const contXmlMaster = "application/vnd.ms-visio.master+xml"
    Public Const contXmlRecordsets = "application/vnd.ms-visio.recordsets+xml"
    Public Const contXmlRecordset = "application/vnd.ms-visio.recordset+xml"
    Public Const contXmlConnections = "application/vnd.ms-visio.connections+xml"
    Public Const contXmlVbaProject = "application/vnd.ms-office.vbaProject"
    Public Const contXmlCustom = "application/vnd.openxmlformats-officedocument.custom-properties+xml"
    Public Const contActivex = "application/vnd.ms-office.activeX"
    'image file = image/x-emf
    Public Const contXmlValidation = "application/vnd.ms-visio.validation+xml"


#End Region


#End Region


#Region " generic methods "




    '
    ' ------------------------- GetRootFolder ------------------------- 
    '
    Public Shared Function GetRootFolder() As String
        ' Get the path to the current directory and its length. 
        Dim rootDir As String = Directory.GetCurrentDirectory()
        Try

            Dim dirLength As Integer = rootDir.Length

            ' If we're in "...\bin\debug", move up to the root. 
            If rootDir.ToLower().EndsWith("\bin\debug") Then
                rootDir = rootDir.Remove(dirLength - 10, 10)

                ' If we're in "...\bin\release", move up to the root. 
            ElseIf rootDir.ToLower().EndsWith("\bin\release") Then
                rootDir = rootDir.Remove(dirLength - 12, 12)
            End If

        Catch ex As System.Exception

            Dim strMethod As String = "getRootFolder"
            Dim strErr As String = strMethod & " " & ex.Message
            MsgBox(strErr, MsgBoxStyle.Exclamation, strMethod)
            'System.Diagnostics.Debug.Print(strErr)

        End Try

        Return rootDir

    End Function


    '
    ' create an xDoc from a an openformat part
    '
    Public Shared Function createXDocument(ByVal pkgPart As OpenXmlPart) _
        As XDocument
        Dim xDoc As XDocument
        Using StreamReader As New StreamReader(pkgPart.GetStream)
            xDoc = XDocument.Load(XmlReader.Create(StreamReader))
        End Using
        Return xDoc
    End Function


    '
    ' create an xDoc from an PackagePart xml string 
    '
    Public Shared Function createXDocument(ByVal txtPart As TextWriter) _
    As XDocument
        Dim xDoc As XDocument
        Using stringReader As New StringReader(txtPart.ToString)
            xDoc = XDocument.Load(XmlReader.Create(stringReader))
        End Using
        Return xDoc
    End Function


    '
    ' test to see if a file has already been included in our collection
    ' we allow it to be graphed because it is a relationship graph
    '
    Private Function existspkgpartPath _
        (ByVal strpkgpartPath As String, _
         ByVal strParent As String, _
         ByRef colInfo As Collection(Of pkgpartInfo)) _
     As Boolean

        Dim blnPathExists As Boolean = False
        Dim pkgpartInfo As pkgpartInfo

        If colInfo Is Nothing Then
            colInfo = New Collection(Of pkgpartInfo)
        End If

        For Each pkgpartInfo In colInfo
            If LCase(pkgpartInfo.pkgpartPath) = LCase(strpkgpartPath) _
                And LCase(pkgpartInfo.pkgpartParent) = LCase(strParent) Then
                blnPathExists = True
                Exit For
            End If
        Next pkgpartInfo

        Return blnPathExists
    End Function


    '
    ' this method is used to prepare datarecordsets and validation 
    ' this is necessary so that we have a list of pages and shapes for the 
    ' row mapping

    Public Sub buildPageAndShapeDictionaries()

        Dim clsVisPages As clsPages = New clsPages

        Try


            Me.dictPageShape.Clear()
            Me.dictPagesID.Clear()

            '
            ' get the shapes and page dictionaries built 
            '
            clsVisPages.visPackage = Me
            Dim colPkgParts As Collection(Of pkgpartInfo) = New Collection(Of pkgpartInfo)
            'get the package parts for the visPages
            colPkgParts = Me.getPkgPartsByContent(contXmlPages)
            If colPkgParts.Count = 1 Then
                Dim pkgInfo As pkgpartInfo = colPkgParts(0)
                clsVisPages.xDoc = getPackagePartAsXDoc(Me.fileCurrent, pkgInfo)
                ' build the colPages  
                clsVisPages.loadPages(Nothing)
                If Me.dictPageShape Is Nothing Then
                    Me.dictPageShape = New Dictionary(Of stcPageShape, stcShapeInfo)
                Else
                    Me.dictPageShape.Clear()
                End If
                Me.dictPageShape = clsVisPages.loadPageShapeDictionary
                Me.dictPagesID = clsVisPages.dictPagesID
            End If


        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        If clsVisPages IsNot Nothing Then
            clsVisPages = Nothing
        End If

    End Sub

    '
    ' pass in an xelement (in xdocument format), and then either create an attribute or an
    ' element dictionary
    '
    Public Sub readAttAndElem _
        (ByVal newDoc As XDocument, _
         ByVal strPrefix As String, _
        ByVal strSection As String)

        Dim strName As String = ""
        Dim strValue As String = ""

        Try


            Me.attDict.Clear()
            Me.elemDict.Clear()
            Me.nsDict.Clear()

            Dim newXNamespace As XNamespace = getNameSpaceFromPrefix(newDoc, strPrefix)

            Dim qryProperties = newDoc.Descendants(newXNamespace + strSection).First

            If qryProperties Is Nothing Then Exit Sub

            Dim listOfAttributes As IEnumerable(Of XAttribute) = _
                    From att In qryProperties.Attributes() _
                    Select att

            If listOfAttributes IsNot Nothing Then
                If 0 < listOfAttributes.Count Then
                    For Each att As XAttribute In listOfAttributes
                        ' need to keep mixed case on names for when we want to drill down
                        ' attribute names should be unique by design
                        attDict.Add(att.Name.LocalName, att.Value)
                    Next att
                End If
            End If



            ' for handling rows
            Dim strIX As String = ""

            ' start the element drilldown
            Dim listOfElements As IEnumerable(Of XElement) = _
                    From elem In qryProperties.Elements() _
                    Select elem
            If listOfElements IsNot Nothing Then
                If 0 < listOfElements.Count Then
                    For Each elem As XElement In listOfElements
                        ' Console.WriteLine("Elem {0}={1}", elem.Name.LocalName, elem.Value
                        ' element names may not be unique so we should check for some of the obvious
                        ' ones and rename them

                        strName = elem.Name.LocalName.ToString
                        strValue = ""
                        Dim stcNewCell As stcCell = New stcCell(Nothing, Nothing, Nothing, Nothing)

                        Select Case LCase(elem.Name.LocalName)
                            Case "cell"
                                stcNewCell.readXElement(elem)
                                strName = stcNewCell.cellNewName
                                strValue = stcNewCell.cellNewValue
                                'Dim newXml As String = stcNewCell.toXML
                                'Debug.Print(newXml)

                            Case "section"
                                ' can have multiple sections with the same name
                                If elem.@IX IsNot Nothing Then
                                    strIX = elem.@IX.ToString
                                    strName = "section" & "_" & elem.@N.ToString & "_" & strIX
                                Else
                                    strName = "section" & "_" & elem.@N.ToString
                                End If
                                Exit Select
                            Case "shapes"
                                GoTo nextElem
                            Case "text"
                                '<xsd:complexType name="TextCell_Type" mixed="true">
                                '	<xsd:choice minOccurs="0" maxOccurs="unbounded">
                                '		<xsd:element name="cp" type="cp_Type" minOccurs="0" maxOccurs="unbounded"/>
                                '		<xsd:element name="pp" type="pp_Type" minOccurs="0" maxOccurs="unbounded"/>
                                '		<xsd:element name="tp" type="tp_Type" minOccurs="0" maxOccurs="unbounded"/>
                                '		<xsd:element name="fld" type="fld_Type" minOccurs="0" maxOccurs="unbounded"/>
                                '	</xsd:choice>
                                '</xsd:complexType>
                                strName = elem.Name.LocalName.ToString
                                Dim qryText = elem.Elements
                                Dim intText As Integer = qryText.Count
                                If intText > 0 Then
                                    Dim firstNode As XElement = qryText.First
                                    strValue = firstNode.Name.LocalName.ToString & "_" & firstNode.@IX.ToString
                                    Exit Select
                                Else
                                    strValue = CType(elem.Value, System.String)
                                    Exit Select
                                End If ' test for multiple sub-elements
                            Case Else

                                Dim qryChildren = elem.DescendantNodes
                                Dim intChildren As Integer = qryChildren.Count
                                strName = elem.Name.LocalName.ToString

                                Select Case intChildren
                                    Case Is < 1
                                        strValue = ""
                                        Exit Select
                                    Case 0, 1
                                        strValue = elem.Value.ToString
                                        If strValue = "" And elem.Attributes.Count > 0 Then
                                            strValue = "has " & elem.Attributes.Count.ToString & " attributes"
                                        End If
                                        Exit Select
                                    Case Is > 1
                                        strValue = "Multiple sub-elements"
                                End Select

                        End Select

                        Try
                            elemDict.Add(strName, strValue)
                            ' check for duplicate names
                        Catch arg As ArgumentException
                            MsgBox("ArgException : " & strName)
                        End Try

nextElem:
                    Next elem
                End If
            End If


        Catch errXml As XmlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(errXml, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    ' pass in a namespace prefix and return the full namespace name
    '
    Public Shared Function getNameSpaceFromPrefix _
                (ByVal newDoc As XDocument, _
                ByVal strPrefix As String) _
                As XNamespace


        Dim nsDict As Dictionary(Of String, System.Xml.Linq.XNamespace) = New Dictionary(Of String, System.Xml.Linq.XNamespace)
        Dim newXNamespace As XNamespace = ""

        nsDict = newDoc.Root.Attributes(). _
            Where(Function(a) a.IsNamespaceDeclaration). _
            GroupBy(Function(a) If(a.Name.[Namespace] = XNamespace.None, [String].Empty, a.Name.LocalName), Function(a) XNamespace.[Get](a.Value)). _
            ToDictionary(Function(g) g.Key, Function(g) g.First())


        Try
            If nsDict.ContainsKey(strPrefix) Then
                newXNamespace = nsDict(strPrefix)
            End If
        Catch err As System.Collections.Generic.KeyNotFoundException
        End Try

        Return newXNamespace

        nsDict = Nothing
        GC.Collect()

    End Function

    '
    ' pass in a dictionary and show the keys and values
    '
    Public Sub loadAttElemLV _
        (ByRef lvControl As ListView, _
         ByVal dictSRC As Dictionary(Of String, String))

        Try

            If lvControl Is Nothing Then Exit Sub


            Dim lvItem As ListViewItem

            Dim strName As String = "NoName"
            Dim strValue As String = "NoValue"

            lvControl.Items.Clear()
            lvControl.Columns.Clear()
            lvControl.View = View.Details
            lvControl.AllowColumnReorder = True
            lvControl.FullRowSelect = True
            lvControl.Columns.Add("Name", 150, HorizontalAlignment.Left)
            lvControl.Columns.Add("Value", 150, HorizontalAlignment.Left)


            For Each field In dictSRC
                strName = "NoName"
                strValue = "NoValue"

                strName = field.Key.ToString
                strValue = field.Value.ToString

                lvItem = New ListViewItem(strName)
                lvItem.SubItems.Add(strValue)

                ' add it to the listview on the data page
                lvControl.Items.Add(lvItem)

            Next field



        Catch errXml As XmlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(errXml, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try
    End Sub


    '
    ' pass in an xdocument and load cell/section information into the cell dictionary
    ' by making it a standalone sub we can apply it to multiple objects (masters, shapes, etc.)
    '
    Public Sub loadDictCellSection _
        (ByVal strSource As String, _
         ByVal xdocSource As XDocument, _
         ByRef dictCS As Dictionary(Of stcCellSection, String))


        Try

            ' make sure the dictionary is built
            If dictCS Is Nothing Then
                dictCS = New Dictionary(Of stcCellSection, String)
            End If

            Dim stcNewCell As stcCell = New stcCell(Nothing, Nothing, Nothing, Nothing)
            Dim xelemParent As XElement = Nothing
            Dim xelemSection As XElement = Nothing
            Dim strSection As String = ""

            ' get the default namespace
            Dim newXNamespace As XNamespace = getNameSpaceFromPrefix(xdocSource, "")
            ' get all of the cell descendants
            Dim qryProperties = xdocSource.Descendants(newXNamespace + "Cell")
            Dim intCells As Integer = qryProperties.Count

            If intCells < 1 Then
                Exit Sub
            Else
                For Each cell In qryProperties
                    ' clear the ancestor data
                    xelemParent = Nothing
                    xelemSection = Nothing
                    strSection = "***"

                    stcNewCell.readXElement(cell)
                    xelemParent = cell.Parent
                    ' if we are in a row, then check the row's parent for the section name
                    If LCase(xelemParent.Name.LocalName) = "row" Then
                        xelemSection = xelemParent.Parent
                        If LCase(xelemSection.Name.LocalName) = "section" Then
                            strSection = xelemSection.@N.ToString
                        End If ' test for section name
                        'Debug.Print(strSection)
                    End If ' test for parent as row

                    Dim blnFound As Boolean = False
                    Dim stcNewCellSection As stcCellSection = New stcCellSection
                    With stcNewCellSection
                        .csSource = strSource
                        .csCellName = stcNewCell.cellName.ToString
                        .csCellParent = strSection
                    End With
                    Try
                        blnFound = dictCS.ContainsKey(stcNewCellSection)
                    Catch err As System.Collections.Generic.KeyNotFoundException
                    End Try
                    If blnFound = False Then dictCS.Add(stcNewCellSection, "")
                Next cell
            End If ' test for cell count


        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub



#End Region


#Region " Package Treeview "


    '
    ' modified from 
    ' MSDN article February 2007. The article was written by Ted Pattison.
    ' Office Space - Building Office Open XML Files
    ' http://msdn.microsoft.com/en-us/magazine/cc163478.aspx
    ' it was modified to capture the scanned package data to a collection so that we
    ' could come back to it without having to reanalyze the package. It was also set
    ' so that we can use it across generic treeviews on different forms.
    '
    Public Sub loadPkgAndParts _
        (Optional ByRef treeControl As TreeView = Nothing)

        If treeControl IsNot Nothing Then
            treeControl.Nodes.Clear()
        End If


        Me.m_colPkgPartInfo = Nothing
        Me.colPkgPartInfo = New Collection(Of pkgpartInfo)


        Try

            Using CurrentPackage As Package = Package.Open(Me.fileCurrent, FileMode.Open, FileAccess.Read)


                'Dim colParts As System.IO.Packaging.PackagePartCollection = CurrentPackage.GetParts
                'For Each part In colParts
                '    Debug.Print(part.Uri.ToString)
                'Next


                Dim PackageName As String = Me.fileCurrent.Substring(Me.fileCurrent.LastIndexOf("\") + 1)
                Dim rootpkgpartInfo As pkgpartInfo = New pkgpartInfo _
                                               (Me.fileCurrent, _
                                                pkgpartTypeEnum.Package, _
                                                "", _
                                                "", _
                                                "", _
                                                "")
                Me.colPkgPartInfo.Add(rootpkgpartInfo)

                Dim rootNode As TreeNode = New TreeNode(PackageName, 0, 0)
                rootNode.Tag = rootpkgpartInfo

                For Each rel As PackageRelationship In CurrentPackage.GetRelationships()
                    Dim PartUri As Uri = System.IO.Packaging.PackUriHelper.ResolvePartUri(rel.SourceUri, rel.TargetUri)
                    Dim part As PackagePart = CurrentPackage.GetPart(PartUri)
                    Dim topLevelPartpkgpartInfo As pkgpartInfo = New pkgpartInfo _
                                                           (part.Uri.OriginalString, _
                                                            pkgpartTypeEnum.Part, _
                                                            PackageName, _
                                                            rel.RelationshipType, _
                                                            part.ContentType, _
                                                            "")
                    Me.colPkgPartInfo.Add(topLevelPartpkgpartInfo)
                    If treeControl IsNot Nothing Then
                        Dim topLevelPartNode As TreeNode = New TreeNode(part.Uri.OriginalString, 1, 1)
                        topLevelPartNode.Tag = topLevelPartpkgpartInfo
                        PopulateTreeChildPartNode(part, topLevelPartNode)
                        rootNode.Nodes.Add(topLevelPartNode)
                    Else
                        PopulateTreeChildPartNode(part, Nothing)
                    End If


                Next rel

                If treeControl IsNot Nothing Then

                    treeControl.Nodes.Add(rootNode)
                    treeControl.ExpandAll()

                    If treeControl.Nodes.Count > 0 Then
                        treeControl.SelectedNode = treeControl.Nodes(0)
                        treeControl.Focus()
                    End If
                End If

                CurrentPackage.Close()

            End Using

        Catch errXml As XmlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(errXml, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try




    End Sub


    '
    '
    '
    Private Sub PopulateTreeChildPartNode _
                (ByVal part As PackagePart, _
                 ByVal partNode As TreeNode)

        Try

            For Each rel As PackageRelationship In part.GetRelationships()
                If rel.TargetMode = TargetMode.Internal Then
                    Dim ChildPartUri As Uri = System.IO.Packaging.PackUriHelper.ResolvePartUri(rel.SourceUri, rel.TargetUri)
                    Dim ChildPart As PackagePart = part.Package.GetPart(ChildPartUri)
                    Dim ChildPartpkgpartInfo As pkgpartInfo = New pkgpartInfo _
                                (ChildPartUri.OriginalString, _
                                 pkgpartTypeEnum.Part, _
                                 part.Uri.OriginalString, _
                                 rel.RelationshipType, _
                                 ChildPart.ContentType, _
                                 rel.Id.ToString)
                    If existspkgpartPath(ChildPartUri.OriginalString, part.Uri.OriginalString, colPkgPartInfo) = False Then
                        colPkgPartInfo.Add(ChildPartpkgpartInfo)
                    Else
                        ' this node has already been added (need to test because of ppt recursion)
                        GoTo NextRel
                    End If ' test for in collection

                    If partNode IsNot Nothing Then
                        Dim ChildPartNode As TreeNode = New TreeNode(rel.TargetUri.OriginalString, 1, 1)
                        ChildPartNode.Tag = ChildPartpkgpartInfo
                        ' add it to the tree
                        partNode.Nodes.Add(ChildPartNode)
                        ' look for children
                        PopulateTreeChildPartNode(ChildPart, ChildPartNode)
                    Else
                        PopulateTreeChildPartNode(ChildPart, Nothing)
                    End If

                ElseIf rel.TargetMode = TargetMode.External Then
                    Dim ExternalFile As String = rel.TargetUri.OriginalString.Substring(rel.TargetUri.OriginalString.LastIndexOf("\") + 1)
                    Dim ExternalRelationshipNode As TreeNode = New TreeNode(ExternalFile, 2, 2)
                    Dim ExternalRelationshippkgpartInfo As pkgpartInfo = New pkgpartInfo _
                            (rel.TargetUri.OriginalString, _
                             pkgpartTypeEnum.ExternalRelationship, _
                             rel.TargetUri.OriginalString, _
                             rel.RelationshipType, _
                             "", _
                             rel.Id.ToString)
                    If existspkgpartPath(rel.TargetUri.OriginalString, rel.TargetUri.ToString, colPkgPartInfo) = False Then
                        colPkgPartInfo.Add(ExternalRelationshippkgpartInfo)
                    End If ' test for in node collection
                    ExternalRelationshipNode.Tag = ExternalRelationshippkgpartInfo
                    If partNode IsNot Nothing Then
                        partNode.Nodes.Add(ExternalRelationshipNode)
                    End If

                    End If
NextRel:
            Next rel

        Catch stk As System.StackOverflowException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(stk, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub



    '  --------------------------- CopyStream ---------------------------
    ''' <summary>
    '''   Copies data from a source stream to a target stream.</summary>
    ''' <param name="source">
    '''   The source stream to copy from.</param>
    ''' <param name="target">
    '''   The destination stream to copy to.</param>
    Public Shared Sub CopyStream(ByVal source As System.IO.Stream, ByVal target As System.IO.Stream)

        Try

            Const bufSize As Integer = &H1000
            Dim buf(bufSize - 1) As Byte
            Dim bytesRead As Integer = 0
            source.Position = 0
            bytesRead = source.Read(buf, 0, bufSize)
            Do While bytesRead > 0
                target.Write(buf, 0, bytesRead)
                bytesRead = source.Read(buf, 0, bufSize)
            Loop

        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


#End Region


#Region " Shapes Treeview "



    '
    ' this assumes that loadshapes has been called using the clsPage module
    '
    Public Sub buildTvShapes _
        (ByRef tvControl As TreeView, _
         ByVal strRootNode As String)

        Try

            tvControl.Nodes.Clear()

            Dim rootNode As TreeNode = New TreeNode(strRootNode, 0, 0)
            tvControl.Nodes.Add(rootNode)

            For Each lngShpId In Me.dictPageShape
                If lngShpId.Value.lngShpParent = -1 Then

                    Dim newPS As stcPageShape
                    With newPS
                        .psPage = lngShpId.Key.psPage
                        .psShape = lngShpId.Key.psShape
                    End With
                    Dim newShape As stcShapeInfo = Me.dictPageShape(newPS)
                    Dim strName As String = newShape.strShpNameU.ToString
                    If LCase(strName) = "noshpnameu" Then
                        strName = "shapeid" & "." & CStr(newShape.lngShpId)
                    End If
                    strName = newShape.strShpType & "_" & strName
                    Dim newNode As TreeNode = New TreeNode(lngShpId.Key.ToString)
                    newNode.Tag = newShape
                    newNode.Text = strName
                    addChildShapes(newPS, newNode)
                    rootNode.Nodes.Add(newNode)
                End If
            Next

            tvControl.ExpandAll()

            If tvControl.Nodes.Count > 0 Then
                tvControl.SelectedNode = tvControl.Nodes(0)
                tvControl.Focus()
            End If




        Catch errXml As XmlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(errXml, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try


    End Sub

    '
    '
    '
    Private Sub addChildShapes(ByVal stcPS As stcPageShape, ByRef shpNode As TreeNode)

        Try

            Dim lngPgId As Long = -1
            Dim lngShpId As Long = -1
            With stcPS
                lngPgId = .psPage
                lngShpId = .psShape
            End With

            For Each shape In Me.dictPageShape
                If CLng(shape.Value.lngShpParent) = lngShpId Then
                    Dim newPS As stcPageShape
                    With newPS
                        .psPage = CLng(shape.Value.lngPgId)
                        .psShape = CLng(shape.Value.lngShpId)
                    End With
                    Dim newShape As stcShapeInfo = Me.dictPageShape(newPS)
                    Dim strName As String = newShape.strShpNameU.ToString
                    If LCase(strName) = "noshpnameu" Then
                        strName = "shapeid" & "." & CStr(newShape.lngShpId)
                    End If
                    strName = newShape.strShpType & "_" & strName
                    Dim newNode As TreeNode = New TreeNode(shape.Key.ToString)
                    newNode.Tag = newShape
                    newNode.Text = strName
                    addChildShapes(newPS, newNode)
                    shpNode.Nodes.Add(newNode)
                End If
            Next shape

        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


    '
    ' 
    '
    Public Sub loadShapes _
                (ByVal tempDoc As XDocument, _
                Optional ByVal lngPgId As Long = -1)

        Try

            Me.dictPageShape.Clear()


            Dim lngShpId As Long
            Dim strShpName As String
            Dim strShpNameU As String
            Dim strShpType As String
            Dim lngParent As Long

            ' get a list of all of the shapes
            Dim qryShapes = tempDoc...<Shape>
            Dim intShapes As Integer = qryShapes.Count


            For Each shape In qryShapes


                lngShpId = -1
                strShpName = "NoShpName"
                strShpNameU = "NoShpNameU"
                strShpType = "NoShpType"
                lngParent = -1

                Dim listOfAttributes As IEnumerable(Of XAttribute) = _
                    From att In shape.Attributes() _
                    Select att

                For Each att As XAttribute In listOfAttributes
                    ' Console.WriteLine("{0}={1}", att.Name.LocalName, att.Value)
                    Select Case LCase(att.Name.LocalName)
                        Case "id"
                            lngShpId = CLng(att.Value)
                        Case "name"
                            ' get the shape name
                            strShpName = CStr(att.Value)
                        Case "nameu"
                            strShpNameU = CStr(att.Value)
                        Case "type"
                            strShpType = CStr(att.Value)
                    End Select
                Next att


                ' start the element drilldown
                Dim listOfElements As IEnumerable(Of XElement) = _
                        From elem In shape.Elements() _
                        Select elem
                For Each elem As XElement In listOfElements
                    ' Console.WriteLine("Elem {0}={1}", elem.Name.LocalName, elem.Value
                    ' element names may not be unique so we should check for some of the obvious
                    ' ones and rename them
                    Dim strName As String = elem.Name.LocalName
                    Dim strValue As String = elem.Value
                    Select Case LCase(elem.Name.LocalName)
                        ' 
                        Case "shapes"
                            loadChildShape(elem, lngShpId, lngPgId)

                    End Select

                Next elem


                ' build pageshapeinfo object
                Dim newPageShape As stcPageShape
                With newPageShape
                    .psPage = lngPgId
                    .psShape = lngShpId
                End With

                ' put the pageid into the page dictionary
                Dim blnFound As Boolean = False
                Try
                    blnFound = Me.dictPageShape.ContainsKey(newPageShape)
                Catch err As System.Collections.Generic.KeyNotFoundException
                End Try

                If blnFound = False Then
                    Dim newShape As stcShapeInfo = New stcShapeInfo _
                                    (lngPgId, _
                                     lngShpId, _
                                     strShpName, _
                                     strShpNameU, _
                                     strShpType, _
                                     lngParent)

                    Me.dictPageShape.Add(newPageShape, newShape)
                End If

            Next shape


        Catch errXml As XmlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(errXml, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


    '
    '
    '
    Private Sub loadChildShape(ByVal shpSource As XElement, ByVal lngParentId As Long, ByVal lngPgId As Long)

        Try


            Dim lngShpId As Long
            Dim strShpName As String
            Dim strShpNameU As String
            Dim strShpType As String


            ' get a list of all of the shapes
            Dim qryShapes = shpSource...<Shape>
            Dim intShapes As Integer = qryShapes.Count

            For Each shape In qryShapes

                lngShpId = -1
                strShpName = "NoShpName"
                strShpNameU = "NoShpNameU"
                strShpType = "NoShpType"


                Dim listOfAttributes As IEnumerable(Of XAttribute) = _
                    From att In shape.Attributes() _
                    Select att

                For Each att As XAttribute In listOfAttributes
                    ' Console.WriteLine("{0}={1}", att.Name.LocalName, att.Value)
                    Select Case LCase(att.Name.LocalName)
                        Case "id"
                            lngShpId = CLng(att.Value)
                        Case "name"
                            ' get the shape name
                            strShpName = CStr(att.Value)
                        Case "nameu"
                            strShpNameU = CStr(att.Value)
                        Case "type"
                            strShpType = CStr(att.Value)
                    End Select
                Next att

                ' start the element drilldown
                Dim listOfElements As IEnumerable(Of XElement) = _
                        From elem In shape.Elements() _
                        Select elem
                For Each elem As XElement In listOfElements
                    ' Console.WriteLine("Elem {0}={1}", elem.Name.LocalName, elem.Value
                    ' element names may not be unique so we should check for some of the obvious
                    ' ones and rename them
                    Dim strName As String = elem.Name.LocalName
                    Dim strValue As String = elem.Value
                    Select Case LCase(elem.Name.LocalName)
                        ' 
                        Case "shapes"
                            loadChildShape(elem, lngShpId, lngPgId)

                    End Select

                Next elem


                ' build pageshapeinfo object
                Dim newPageShape As stcPageShape
                With newPageShape
                    .psPage = lngPgId
                    .psShape = lngShpId
                End With

                ' put the pageid into the page dictionary
                Dim blnFound As Boolean = False
                Try
                    blnFound = Me.dictPageShape.ContainsKey(newPageShape)
                Catch err As System.Collections.Generic.KeyNotFoundException
                End Try

                If blnFound = False Then
                    Dim newShape As stcShapeInfo = New stcShapeInfo _
                                    (lngPgId, _
                                     lngShpId, _
                                     strShpName, _
                                     strShpNameU, _
                                     strShpType, _
                                     lngParentId)

                    Me.dictPageShape.Add(newPageShape, newShape)
                End If

            Next shape


        Catch errXml As XmlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(errXml, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub



#End Region




    '
    '
    '
#Region " Content "



    '
    ' go read the pkgpartInfo collection for contenttype to determine what type of document has
    ' been read. save the response to the doctype property
    '
    Public Function getDocType() As String

        Dim docType As String = doctypeUnknown
        Dim strContent As String = ""

        For Each PkgPart In colPkgPartInfo
            strContent = PkgPart.pkgpartContentType
            If strContent.Contains("vnd.ms-visio.drawing") Then
                Me.docType = doctypeVisio
                Exit For
            ElseIf strContent.Contains("vnd.openxmlformats-officedocument.spreadsheetml") Then
                Me.docType = doctypeExcel
                Exit For
            ElseIf strContent.Contains("vnd.openxmlformats-officedocument.wordprocessingml.document") Then
                Me.docType = doctypeWord
                Exit For
            ElseIf strContent.Contains("vnd.openxmlformats-officedocument.presentationml.presentation") Then
                Me.docType = doctypePwrPnt
                Exit For
            End If
        Next PkgPart

        Return Me.docType

    End Function

    '
    '
    '
    Public Function getPkgPartsByContent _
            (ByVal strContent As String) _
            As Collection(Of pkgpartInfo)

        Dim colReturn As Collection(Of pkgpartInfo) = New Collection(Of pkgpartInfo)

        Try

            For Each PkgPart In colPkgPartInfo

                If LCase(PkgPart.pkgpartContentType) = LCase(strContent) Then
                    colReturn.Add(PkgPart)
                End If ' test for content type
            Next PkgPart

        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        Return colReturn
    End Function

    '
    '
    '
    Public Function getPackagePartAsXDoc _
        (ByVal packagePath As String, _
         ByVal currentPkgPart As pkgpartInfo) _
        As XDocument

        Dim xdocReturn As XDocument = Nothing

        Try

            If (currentPkgPart.pkgpartType = pkgpartTypeEnum.Part) And _
                (currentPkgPart.pkgpartPath.Contains(".xml")) Then

                Using CurrentPackage As Package = Package.Open(Me.fileCurrent, FileMode.Open, FileAccess.Read)

                    Dim part As PackagePart = CurrentPackage.GetPart(New Uri(currentPkgPart.pkgpartPath, UriKind.Relative))
                    '  Load the document XML in the part into an XDocument instance.
                    Dim myXmlReaderSettings As XmlReaderSettings = New XmlReaderSettings
                    myXmlReaderSettings.IgnoreComments = True
                    myXmlReaderSettings.IgnoreWhitespace = False
                    myXmlReaderSettings.ConformanceLevel = ConformanceLevel.Document
                    myXmlReaderSettings.IgnoreProcessingInstructions = False
                    xdocReturn = XDocument.Load(XmlReader.Create(part.GetStream(), myXmlReaderSettings))

                    CurrentPackage.Close()

                End Using

            End If ' test for xml part

        Catch errXml As XmlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(errXml, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Catch err As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(err, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try


        Return xdocReturn

    End Function

    '
    '
    '
    Private Function getContentType(ByVal strContent As String) As String

        Dim strType As String = ""

        Dim strDoc As String = strContent
        If strDoc.Contains("vnd.ms-visio.drawing") Then
            strType = "document"
            GoTo returnFunc
        End If

        Select Case LCase(strContent)
            Case LCase(contXmlApp)
                strType = "application"
            Case LCase(contXmlCore)
                strType = "core"
            Case LCase(contXmlWindows)
                strType = "windows"
            Case LCase(contXmlPages)
                strType = "pages"
            Case LCase(contXmlPage)
                strType = "page"
            Case LCase(contXmlMasters)
                strType = "masters"
            Case LCase(contXmlMaster)
                strType = "master"
            Case LCase(contXmlRecordsets)
                strType = "recordsets"
            Case LCase(contXmlRecordset)
                strType = "recordset"
            Case LCase(contXmlConnections)
                strType = "dataconnections"
            Case LCase(contXmlVbaProject)
                strType = "vbaproject"
            Case LCase(contXmlCustom)
                strType = "customproperties"
            Case LCase(contActivex)
                strType = "activex"
            Case LCase(contXmlValidation)
                strType = "validation"
            Case Else
                strType = "unknown"

        End Select

returnFunc:
        Return strType
    End Function

    '
    ' test to see if content type exists in the package
    '
    Public Function existsContentType(ByVal contentType As String) As Boolean

        Dim blnContentExists As Boolean = False
        Dim pkgpartInfo As pkgpartInfo

        If Me.colPkgPartInfo Is Nothing Then
            Me.colPkgPartInfo = New Collection(Of pkgpartInfo)
        End If

        For Each pkgpartInfo In Me.colPkgPartInfo
            If LCase(pkgpartInfo.pkgpartContentType) = LCase(contentType) Then
                blnContentExists = True
                Exit For
            End If
        Next pkgpartInfo

        Return blnContentExists
    End Function



#End Region

    '
    '
    '
    Public Sub New()

        Me.m_pkgCurrent = Nothing
        Me.m_fileCurrent = ""
        Me.m_xDoc = Nothing
        Me.m_colPkgPartInfo = Nothing
        colPkgPartInfo = New Collection(Of pkgpartInfo)

        ' need a dictionary of the shapes in the document (pageid, shapeid)
        Me._dictPageShape = Nothing
        Me.dictPageShape = New Dictionary(Of stcPageShape, stcShapeInfo)

        Me.m_dictPagesID = Nothing
        Me.dictPagesID = New Dictionary(Of Integer, stcPageData)


        ' use these for our generic read att/elem
        Me.m_nsDict = Nothing
        Me.nsDict = New Dictionary(Of String, System.Xml.Linq.XNamespace)
        Me.m_attDict = Nothing
        Me.attDict = New Dictionary(Of String, String)
        Me.m_elemDict = Nothing
        Me.elemDict = New Dictionary(Of String, String)

        Me.docType = doctypeUnknown

    End Sub

    '
    '
    '
    Protected Overrides Sub Finalize()

        If Me.m_pkgCurrent IsNot Nothing Then
            Me.m_pkgCurrent = Nothing
        End If

        If Me.m_xDoc IsNot Nothing Then
            Me.m_xDoc = Nothing
        End If

        If Me.m_colPkgPartInfo IsNot Nothing Then
            Me.m_colPkgPartInfo = Nothing
        End If

        If Me._dictPageShape IsNot Nothing Then
            Me._dictPageShape = Nothing
        End If



        GC.Collect()

        MyBase.Finalize()
    End Sub


End Class
